﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dora.Orm.SqlSugarCore.UnitOfWork;
using Dora.Services.AdminUser.Dto.QueryDto;
using Dora.Services.AdminUser.Dto.ViewDto;
using Dora.Services.AdminUser.Entities;
using Dora.Tools.Enums.AdminUser;
using Dora.Tools.Utility.Models;
using SqlSugar;
using Surging.Core.Common;

namespace Dora.Services.AdminUser.Repository.Repositorys
{
    /// <summary>
    /// 组织机构表仓储层
    /// </summary>
    public class OrganizationRepository : SqlSugarRepository<OrganizationEntity, int>
    {
        private ISugarUnitOfWork _unitOfWork;
        public OrganizationRepository(ISugarUnitOfWork unitOfWork) : base(unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        /// <summary>
        /// 保存组织机构信息
        /// </summary>
        /// <param name="organization"></param>
        /// <returns></returns>
        public int SaveOrganizationInfo(OrganizationEntity organization, SqlSugarClient modifyClient = null)
        {
            if (modifyClient == null) modifyClient = _unitOfWork.ModifyClient;
            var orgInfo = modifyClient.Queryable<OrganizationEntity>().First(x => x.Id == organization.Id && x.IsDeleted == 0);
            if (orgInfo == null)
            {
                var result = modifyClient.Insertable(organization).ExecuteReturnEntity();
                return result.Id;
            }
            else
            {
                orgInfo.Name = organization.Name;
                orgInfo.ShortName = organization.ShortName;
                //orgInfo.ShortCode = organization.ShortCode;
                //orgInfo.LevelIdx = organization.LevelIdx;
                //orgInfo.LevelPath = organization.LevelPath;
                orgInfo.ParentId = organization.ParentId;
                orgInfo.Sort = organization.Sort;
                //orgInfo.HasSub = organization.HasSub;
                orgInfo.OrgLevel = organization.OrgLevel;
                //orgInfo.OrgType = organization.OrgType;
                orgInfo.ProvinceId = organization.ProvinceId;
                orgInfo.ProvinceName = organization.ProvinceName;
                orgInfo.CityId = organization.CityId;
                orgInfo.CityName = organization.CityName;
                orgInfo.DistrictId = organization.DistrictId;
                orgInfo.DistrictName = organization.DistrictName;
                orgInfo.TownId = organization.TownId;
                orgInfo.TownName = organization.TownName;
                orgInfo.Address = organization.Address;
                orgInfo.Latitude = organization.Latitude;
                orgInfo.Longitude = organization.Longitude;
                orgInfo.UpdatedUserId = organization.UpdatedUserId;
                orgInfo.UpdatedUserName = organization.UpdatedUserName;
                orgInfo.UpdatedTime = organization.UpdatedTime;
                orgInfo.OrgLicenseKeyUrl = organization.OrgLicenseKeyUrl;
                orgInfo.OrgLogoUrl = organization.OrgLogoUrl;
                orgInfo.OrgProfile = organization.OrgProfile;

                orgInfo.LeaderName = organization.LeaderName;
                orgInfo.LeaderIDCardNumber = organization.LeaderIDCardNumber;
                orgInfo.LeaderPhone = organization.LeaderPhone;
                orgInfo.EmailAddress = organization.EmailAddress;
                orgInfo.Remark = organization.Remark;

                if (orgInfo.OrgType == (int)OrgType.General)
                {
                    orgInfo.BizLicenseKey = organization.BizLicenseKey;
                    orgInfo.IsNeedPaperContract = organization.IsNeedPaperContract;
                    modifyClient.Updateable(orgInfo).UpdateColumns(it=>new { it.IsNeedPaperContract,it.UpdatedUserId,it.UpdatedUserName,it.UpdatedTime})
                        .Where(x=>x.LevelPath.StartsWith(orgInfo.LevelPath)).ExecuteCommand();
                }

                //除了直属门店， 大社、以及所有的分销商都有统一社会信用代码
                if (orgInfo.OrgType != (int)OrgType.DirectlyUnder)
                {
                    orgInfo.CreditCode = organization.CreditCode;
                }

                //分销商
                if (orgInfo.OrgType == (int)OrgType.Distributor)
                {
                    //【分销商】分社
                    if (orgInfo.OrgLevel == (int)OrgLevel.Branch)
                    {
                        orgInfo.DepositMoney = organization.DepositMoney;
                        orgInfo.TaxPaymentType = organization.TaxPaymentType;
                        orgInfo.IsAllowContractSupplier = organization.IsAllowContractSupplier;
                        orgInfo.IsContractInsuranceSupplier = organization.IsContractInsuranceSupplier;
                        orgInfo.IsAllowOpenStores = organization.IsAllowOpenStores;
                        if (orgInfo.IsAllowOpenStores == (int)AllowAllowOpenStores.Yes) orgInfo.IsAllowAudit = organization.IsAllowAudit;
                    }

                    //【分销商】门店
                    if (orgInfo.OrgLevel == (int)OrgLevel.Store)
                    {
                        orgInfo.DepositMoney = organization.DepositMoney;
                        orgInfo.GrantingCredit = organization.GrantingCredit;
                        orgInfo.WarningEdgeValue = organization.WarningEdgeValue;
                    }
                }

                orgInfo.TagIds = organization.TagIds;
                orgInfo.TagNames = organization.TagNames;

                var result = modifyClient.Updateable(orgInfo).IgnoreColumns(it => new
                    { it.CreatedTime, it.CreatedUserId, it.CreatedUserName, it.HasSub, it.IsDeleted, it.LevelIdx, it.LevelPath, it.RootId, it.IsDisabled, it.IsFrozen })
                        .ExecuteCommandHasChange();
                return orgInfo.Id;
            }
        }

        /// <summary>
        /// 删除组织机构信息
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<bool> DeleteOrganization(int orgId)
        {
            var orgInfo = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().FirstAsync(x => x.Id == orgId && x.IsDeleted == 0);
            if (orgInfo == null)
                throw new ArgumentNullException($"找不到Id为{orgId}的组织机构信息");

            //查询下级机构
            var subOrgs = _unitOfWork.QueryClient.Queryable<OrganizationEntity>().Where(x=> (x.LevelPath==orgInfo.LevelPath || x.LevelPath.StartsWith(orgInfo.LevelPath)) || x.IsDeleted==0).ToList();
            var subOrgIds = subOrgs.Select(x=>x.Id);

            var modifyClient = _unitOfWork.ModifyClient;
            try
            {
                modifyClient.Ado.BeginTran();
                orgInfo.IsDeleted = 1;

                //删除下级机构用户关系
                var result = modifyClient.Ado.ExecuteCommand("delete from tas_org_user_map where orgId in (?OrgIds)", new SugarParameter("OrgIds", string.Join(",",subOrgIds))) >= 0;

                //删除下级组织机构
                result &= modifyClient.Updateable(orgInfo).UpdateColumns(it => new { it.IsDeleted, it.Id }).Where(x => subOrgIds.Contains(x.Id) && x.IsDeleted == 0).ExecuteCommandHasChange();

                modifyClient.Ado.CommitTran();
                return result;
            }
            catch (Exception ex)
            {
                modifyClient.Ado.RollbackTran();
                Console.WriteLine(ex);
                throw ex;
            }
        }

        /// <summary>
        /// 根据主键查询组织机构信息
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<OrganizationEntity> GetOrganizationInfoById(int orgId)
        {
            var orgInfo = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().FirstAsync(x => x.Id == orgId && x.IsDeleted == 0);
            return orgInfo;
        }

        /// <summary>
        /// 查询指定机构Id及下属机构集合
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<List<OrganizationEntity>> GetSubOrganizations(int orgId,string keywords = null)
        {
            var orgInfo = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().FirstAsync(x => x.Id == orgId && x.IsDeleted == 0);
            if (orgInfo == null)
                throw new ArgumentNullException($"找不到Id为{orgId}的组织机构信息");

            var subList = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().
                Where(x=> x.LevelPath==orgInfo.LevelPath || x.LevelPath.StartsWith(orgInfo.LevelPath))
                .WhereIF(!string.IsNullOrWhiteSpace(keywords),x=>x.Name.Contains(keywords))
                .Where(x => x.IsDeleted == 0)
                .ToListAsync();
            return subList;
        }

        /// <summary>
        /// 查询指定Id的组织机构的下一级机构列表
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<List<OrganizationEntity>> GetSubOrgList(int orgId)
        {
            var subList = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().Where(x => x.ParentId == orgId && x.IsDeleted == 0).ToListAsync();
            return subList;
        }


        /// <summary>
        /// 查询指定Id的组织机构（刷新用户权限使用）
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<OrgUserResercheCacheDto> GetOrgUserResercheCacheInfo(int orgId)
        {
            var redisReserche = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().Where(x => x.Id == orgId)
                .Select(it=> new OrgUserResercheCacheDto { Id=it.Id, Name=it.Name, IsAllowContractSupplier =it.IsAllowContractSupplier
                ,IsContractInsuranceSupplier=it.IsContractInsuranceSupplier,IsAllowOpenStores=it.IsAllowOpenStores
                }).FirstAsync();
            return redisReserche;
        }

        /// <summary>
        /// 查询机构Id的指定级别的下一级组织机构列表
        /// </summary>
        /// <param name="orgId"></param>
        /// <param name="orgLevel"></param>
        /// <returns></returns>
        public async Task<List<OrganizationEntity>> GetOrgList(int orgId, OrgLevel orgLevel)
        {
            var subList = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().Where(x => x.ParentId == orgId && x.IsDeleted == 0)
                .WhereIF(orgLevel != OrgLevel.Store, x => x.OrgLevel == (int)orgLevel)
                .WhereIF(orgLevel == OrgLevel.Store, x => x.OrgLevel == (int)OrgLevel.Store || x.OrgLevel == (int)OrgLevel.DirectlyUnder).ToListAsync();
            return subList;
        }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageDataList<OrganizationEntity>> GetPageList(OrganizationQueryDto input)
        {
            var searchPredict = _unitOfWork.QueryClient.Queryable<OrganizationEntity>()
                .Where(it => it.IsDeleted == 0)
                .WhereIF(input.ParentOrgId.HasValue,it=>it.ParentId==input.ParentOrgId)
                .WhereIF(!string.IsNullOrEmpty(input.Keywords), it => it.Name.Contains(input.Keywords.Trim()) || it.ShortName.Contains(input.Keywords.Trim()));
            var totalCount = await searchPredict.CountAsync();
            var pageList = await searchPredict.OrderBy(it => it.Id, OrderByType.Desc).ToPageListAsync(input.PageIndex, input.PageSize);
            var page = new PageDataList<OrganizationEntity>(totalCount, pageList, input.PageIndex, input.PageSize);
            return page;
        }

        /// <summary>
        /// 根据条件查询组织机构Id
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public async Task<List<OrganizationEntity>> GetOrgEntityList(OrganizationQueryDto query)
        {
            var orgList = await _unitOfWork.QueryClient.Queryable<OrganizationEntity>().Where(x => x.IsDeleted == 0)
                .WhereIF(query.ParentOrgId > 0, x => x.RootId == query.ParentOrgId)
                .WhereIF(query.ParentOrgId ==0, x => x.RootId == query.OrgId)
                .WhereIF(!string.IsNullOrEmpty(query.Keywords), x => x.Name.Contains(query.Keywords) || x.ShortName.Contains(query.Keywords))
                .WhereIF(!string.IsNullOrEmpty(query.Leader), x => x.LeaderName.Contains(query.Leader))
                .WhereIF(!string.IsNullOrEmpty(query.Mobile), x => x.LeaderPhone.Contains(query.Mobile))
                .WhereIF(query.OrgLevel > 0, x => x.OrgLevel == query.OrgLevel)
                .WhereIF(query.ProvinceId > 0, x => x.ProvinceId == query.ProvinceId)
                .WhereIF(query.CityId > 0, x => x.CityId == query.CityId)
                .WhereIF(query.DistrictId > 0, x => x.DistrictId == query.DistrictId)
                .WhereIF(!string.IsNullOrEmpty(query.ParentOrgName),x=>SqlFunc.Subqueryable<OrganizationEntity>().Where(o=>o.Id==x.ParentId &&o.Name.Contains(query.ParentOrgName)).Any())
                .ToListAsync();
            return orgList;
        }

        /// <summary>
        /// 查询用户所属的机构列表
        /// </summary>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<List<OrgDto>> GetBriefListByUserId(Guid userId)
        {
            var list = await _unitOfWork.QueryClient.Queryable<OrganizationEntity, TasOrgUserMapEntity>((o, tou) => new object[] {
                JoinType.Inner, o.Id == tou.OrgId
            })
                .Where((o, tou) => tou.UserId == userId && o.IsDeleted == 0)
                .Select((o, tou) => new OrgDto {Id=o.Id,Name=o.Name,LevelPath=o.LevelPath }).ToListAsync();
            return list;
        }

        /// <summary>
        /// 查询用户组织机构映射信息
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="orgId"></param>
        /// <returns></returns>
        public async Task<TasOrgUserMapEntity> GetOrgUserMapInfo(Guid userId,int orgId)
        {
            var mapEntity = await _unitOfWork.QueryClient.Queryable<OrganizationEntity, TasOrgUserMapEntity>((o, tou) => new object[] {
                JoinType.Inner, o.Id == tou.OrgId })
            .Where((o, tou) => tou.UserId == userId && tou.OrgId==orgId && o.IsDeleted == 0)
            .Select((o, tou) => tou).FirstAsync();
            return mapEntity;
        }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageDataList<TasOrgListItemDto>> GetBranchOrgPageList(OrganizationQueryDto input)
        {

            var pagePredict = _unitOfWork.QueryClient.Queryable<OrganizationEntity, OrganizationEntity>((a, b) => new object[] {
                JoinType.Left,a.ParentId==b.Id
            })
                .Where((a, b) => a.IsDeleted == 0)
                .WhereIF(!input.IsStoreContainDirectlyUnder.HasValue || input.IsStoreContainDirectlyUnder == false, (a, b) => a.OrgLevel == input.OrgLevel)
                .WhereIF(input.IsStoreContainDirectlyUnder == true&&input.OrgLevel==(int)OrgLevel.Store, (a, b) => a.OrgLevel == (int)OrgLevel.Store || a.OrgLevel == (int)OrgLevel.DirectlyUnder)
                .WhereIF(input.ParentOrgId.HasValue,(a, b) => a.ParentId == input.ParentOrgId)
                .WhereIF(!string.IsNullOrEmpty(input.LevelPath), (a, b) => a.LevelPath.StartsWith(input.LevelPath))
                .WhereIF(!string.IsNullOrEmpty(input.Keywords), (a, b) => a.Name.Contains(input.Keywords) || a.ShortName.Contains(input.Keywords))
                .WhereIF(!string.IsNullOrEmpty(input.Leader), (a, b) => a.LeaderName.Contains(input.Leader))
                .WhereIF(!string.IsNullOrEmpty(input.Mobile), (a, b) => a.LeaderPhone.Contains(input.Mobile))
                .WhereIF(!string.IsNullOrEmpty(input.ParentOrgName), (a, b) => b.Name.Contains(input.ParentOrgName))
                .WhereIF(input.Status == 1, (a, b) => a.IsFrozen == 0 && a.IsDisabled == 0) // 正常
                .WhereIF(input.Status == 2, (a, b) =>  a.IsDisabled == 1) // 禁用
                .WhereIF(input.Status == 3, (a, b) => a.IsFrozen == 1) // 冻结
                .WhereIF(input.AuditState == StoreAuditState.NoAudit, (a, b) => a.IsAllowAudit == 50)
                .WhereIF(input.AuditState == StoreAuditState.Pending, (a, b) => a.AuditState == (short)StoreAuditState.Pending)
                .WhereIF(input.AuditState == StoreAuditState.Passed, (a, b) => a.AuditState == (short)StoreAuditState.Passed)
                .WhereIF(input.AuditState == StoreAuditState.Rejected, (a, b) =>  a.AuditState == (short)StoreAuditState.Rejected)
                .WhereIF(input.IsDirectly.HasValue && input.IsDirectly == true, (a, b) => a.ParentId == input.DirectlyParentOrgId && a.OrgLevel == (int)OrgLevel.DirectlyUnder)
                .WhereIF(input.IsDirectly.HasValue && input.IsDirectly == false, (a, b) => (a.ParentId == input.DirectlyParentOrgId && a.OrgLevel == (int)OrgLevel.Store)|| (a.ParentId != input.DirectlyParentOrgId && (a.OrgLevel == (int)OrgLevel.Store || a.OrgLevel == (int)OrgLevel.DirectlyUnder)))

                ;
            var totalCount = await pagePredict.CountAsync();
            var pageList = await pagePredict.OrderBy((a, b) => a.CreatedTime, OrderByType.Desc)
                .Select((a, b) => new TasOrgListItemDto
                {
                    Id = a.Id,
                    Name = a.Name,
                    ShortName = a.ShortName,
                    ParentId = a.ParentId,
                    //OrgType = a.OrgType,
                    OrgLevel = a.OrgLevel,
                    HasSub = a.HasSub,
                    //CreditCode = a.CreditCode,
                    LeaderName = a.LeaderName,
                    LeaderPhone = a.LeaderPhone,
                    ProvinceName = a.ProvinceName,
                    CityName = a.CityName,
                    DistrictName = a.DistrictName,
                    TownName = a.TownName,
                    Address = a.Address,
                    DepositMoney = a.DepositMoney,
                    GrantingCredit = a.GrantingCredit,
                    ParentOrgName = b.Name,
                    ParentOrgLevel = b.OrgLevel,
                    IsDisabled = a.IsDisabled,
                    IsFrozen = a.IsFrozen,
                    AuditState = a.AuditState,
                    ShortCode = a.ShortCode,
                    SupplierCount = a.SupplierCount,
                    CreatedTime = a.CreatedTime,
                    TagIds = a.TagIds,
                    TagNames = a.TagNames,
                })
                .ToPageListAsync(input.PageIndex, input.PageSize);

            var page = new PageDataList<TasOrgListItemDto>(totalCount, pageList, input.PageIndex, input.PageSize);
            Console.WriteLine($"GetBranchOrgPageList totalCount:{totalCount} pageList:{pageList.Count}");
            return page;
            #region Old

            //StringBuilder sql = new StringBuilder($@"select a.Id,a.`Name`,a.ShortName,a.ParentId,a.Sort,a.OrgLevel,a.OrgType,a.HasSub,a.CreditCode,
            //    a.LeaderName,a.LeaderPhone,a.LeaderIDCardNumber,a.EmailAddress,a.DepositMoney,a.GrantingCredit,a.WarningEdgeValue,
            //    a.TaxPaymentType,a.IsAllowContractSupplier,a.IsContractInsuranceSupplier,a.IsAllowOpenStores,a.IsAllowAudit,
            //    a.ProvinceId,a.ProvinceName,a.CityId,a.CityName,a.DistrictId,a.DistrictName,a.TownId,a.TownName,a.Address,
            //    a.Latitude,a.Longitude,a.IsDisabled,a.IsFrozen,a.Remark,a.ShortCode,a.AuditState,a.TagIds,a.TagNames,a.SupplierCount,a.FreezeType,b.`Name` as ParentOrgName
            //    from organization a 
            //    left join organization b on a.ParentId = b.Id
            //    where 1=1 ");
            //sql.Append(" and a.IsDeleted=0 ");
            //sql.Append($" and a.LevelPath like CONCAT('%',?ParentOrgId,',%') ");
            //sql.Append($" and a.OrgLevel=?OrgLevel ");
            ////名称
            //if (!string.IsNullOrEmpty(input.Keywords))
            //{
            //    sql.Append($" and (a.Name like CONCAT('%',?Keywords,'%') or a.ShortName like CONCAT('%',?Keywords,'%'))  ");
            //}
            ////负责人姓名
            //if (!string.IsNullOrEmpty(input.Leader))
            //{
            //    sql.Append($" and a.LeaderName like CONCAT('%',?Leader,'%') ");
            //}
            ////联系电话
            //if (!string.IsNullOrEmpty(input.Mobile))
            //{
            //    sql.Append($" and a.LeaderPhone like CONCAT('%',?Mobile,'%') ");
            //}
            ////所属企业名称
            //if (!string.IsNullOrEmpty(input.ParentOrgName))
            //{
            //    sql.Append($" and b.`Name` like CONCAT('%',?ParentOrgName,'%') ");
            //}
            ////状态
            //if(input.Status > 0 && input.Status<=4)
            //{
            //    if (input.Status == 1)  //正常
            //    {
            //        sql.Append(" and a.IsFrozen=0 and a.IsDisabled=0 ");
            //    }else if(input.Status == 2)  //禁用
            //    {
            //        sql.Append(" and a.IsDisabled=0 ");
            //    }else if(input.Status == 3)  // 单体冻结
            //    {
            //        sql.Append(" and a.IsFrozen=1 ");
            //    }else if (input.Status == 4)  // 全部冻结
            //    {
            //        sql.Append(" and a.IsFrozen=2 ");
            //    }

            //}
            #endregion
            //if(input.AuditState > 0 && input.AuditState<=4)
            //{
            //    if (input.AuditState == 1)  //无审核
            //    {
            //        sql.Append(" and a.IsAllowAudit=50 ");
            //    }
            //    else if (input.AuditState == 2)  //待审核
            //    {
            //        sql.Append($" and a.IsAllowAudit=51 and a.AuditState={(int)StoreAuditState.Pending} ");
            //    }
            //    else if (input.AuditState == 3)  //已审核
            //    {
            //        sql.Append($" and a.IsAllowAudit=51 and (a.AuditState={(int)StoreAuditState.Passed}) ");
            //    }
            //    else if (input.AuditState == 4)  //已驳回
            //    {
            //        sql.Append($" and a.IsAllowAudit=51 and (a.AuditState={(int)StoreAuditState.Rejected}) ");
            //    }
            //}

            //try
            //{
            //    //筛选查询结果
            //    var searchPredict = _unitOfWork.QueryClient.SqlQueryable<TasOrgListItemDto>(sql.ToString()).AddParameters(new SugarParameter[] {
            //        new SugarParameter("ParentOrgId",input.ParentOrgId),
            //        new SugarParameter("OrgLevel",input.OrgLevel),
            //        new SugarParameter("Keywords",input.Keywords),
            //        new SugarParameter("Leader",input.Leader),
            //        new SugarParameter("Mobile",input.Mobile),
            //        new SugarParameter("ParentOrgName",input.ParentOrgName),
            //        new SugarParameter("Status",input.Status),
            //        new SugarParameter("AuditState",input.AuditState)
            //    });

            //    var totalCount = await searchPredict.CountAsync();
            //    var items = await searchPredict.ToPageListAsync(input.PageIndex, input.PageSize);
            //    return new PageDataList<TasOrgListItemDto>(totalCount, items, input.PageIndex, input.PageSize);
            //}
            //catch(Exception ex)
            //{
            //    Console.WriteLine(ex.Message);
            //}
            //return null;
        }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<OrganizationEntity>> GetSubBranchOrgList(int orgId, int orgLevel)
        {
            StringBuilder sql = new StringBuilder($@"SELECT * FROM `organization` 
                WHERE 1=1 ");
            sql.Append(" AND IsDeleted=0 ");
            sql.Append(" AND ParentId=?ParentId ");
            sql.Append(" AND OrgLevel=?OrgLevel ");
            sql.Append($" AND Id NOT IN(SELECT DISTINCT OrgId FROM `tas_org_user_map` WHERE IsLeader=1) ");

            try
            {
                //筛选查询结果
                var orgList = await _unitOfWork.QueryClient.SqlQueryable<OrganizationEntity>(sql.ToString()).AddParameters(new SugarParameter[] {
                    new SugarParameter("ParentId",orgId),
                    new SugarParameter("OrgLevel",orgLevel),
                }).ToListAsync();

                return orgList;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return null;
        }

        /// <summary>
        /// 查询指定levelPath的下级组织机构简单信息列表
        /// </summary>
        /// <param name="levelPath"></param>
        /// <returns></returns>
        public async Task<List<OrgSimpleDto>> GetSubSimpleOrgList(string levelPath)
        {
            if (string.IsNullOrWhiteSpace(levelPath)) return new List<OrgSimpleDto>();

            var sql = "SELECT Id,`Name`,ShortName,OrgLevel,LevelPath,TaxPaymentType FROM organization WHERE LevelPath LIKE CONCAT(?LevelPath,'%') AND IsDeleted=0 AND IsDisabled=0 AND IsFrozen=0 ORDER BY OrgLevel,Id ";
            var sugarParams = new SugarParameter[] {
                    new SugarParameter("LevelPath",$"{levelPath}"),
                };
            var orgList = await _unitOfWork.QueryClient.SqlQueryable<OrgSimpleDto>(sql).AddParameters(sugarParams).ToListAsync();
            return orgList;
        }

        /// <summary>
        /// 根据IdList查询机构列表
        /// </summary>
        /// <param name="levelPath"></param>
        /// <returns></returns>
        public async Task<List<OrgSimpleDto>> GetListByIdList(List<int> idList)
        {
            if (idList==null|| idList.Count==0) return new List<OrgSimpleDto>();
            var list =await _unitOfWork.QueryClient.Queryable<OrganizationEntity, OrganizationEntity>((o1,o2)=>new object[] { JoinType.Right,o1.Id==o2.ParentId})
                .Where((o1, o2) => o2.IsDeleted == 0 && idList.Contains(o2.Id))
                .Select((o1, o2) => new OrgSimpleDto
                {
                    Id = o2.Id,
                    Name = o2.Name,
                    ShortName = o2.ShortName,
                    OrgLevel = o2.OrgLevel,
                    LevelPath = o2.LevelPath,
                    CityName = o2.CityName,
                    LeaderName= o2.LeaderName,
                    LeaderPhone= o2.LeaderPhone,
                    ParentId=o1.Id,
                    ParentOrgName=o1.Name
                    
                }).ToListAsync() ;
            return list;
        }

        /// <summary>
        /// 查询距离位置附近的门店信息列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public async Task<PageDataList<NearestStoreOrgDto>> GetNearestStoreOrgPageList(NearestStoreQueryDto query)
        {
            var startIndex = (query.PageIndex - 1) * query.PageSize;
            var parameters = new SugarParameter[] {
                new SugarParameter("TopOrgId",query.TopOrgId),
                new SugarParameter("lng",query.Lng),
                new SugarParameter("lat",query.Lat),
                new SugarParameter("dist",query.DistRange),
                new SugarParameter("startIndex",startIndex),
                new SugarParameter("number",query.PageSize)
            };
            //var dt = await _unitOfWork.QueryClient.Ado.UseStoredProcedure().GetDataTableAsync("p_geo_dist", parameters);
            var dt = await _unitOfWork.QueryClient.Ado.GetDataTableAsync("call p_geo_dist(@TopOrgId,@lng,@lat,@dist,@startIndex,@number)", new { TopOrgId = query.TopOrgId, lng = query.Lng, lat = query.Lat, dist = query.DistRange, startIndex= startIndex,number= query.PageSize });

            if (dt == null || dt.Rows.Count == 0) return new PageDataList<NearestStoreOrgDto>();

            var nearStoreList = _unitOfWork.QueryClient.Utilities.DataTableToList<NearestStoreOrgDto>(dt);
            var totalCount = await _unitOfWork.QueryClient.Ado.GetIntAsync("call p_geo_dist_count (@TopOrgId,@lng,@lat,@dist)", new { TopOrgId = query.TopOrgId, lng = query.Lng, lat = query.Lat, dist = query.DistRange });

            var pagedStores = new PageDataList<NearestStoreOrgDto>(totalCount, nearStoreList, query.PageIndex, query.PageSize);
            return pagedStores;
        }

        public async Task<PageDataList<OrgSimpleDto>> GetSubStoreListAsync(OrganizationQueryDto queryInfo)
        {
            var sqlStr = new StringBuilder();
            sqlStr.Append("select * from organization");
            sqlStr.Append($@" where  LevelPath like '{queryInfo.LevelPath}%' and OrgLevel>{(int)OrgLevel.Branch}");
            if(queryInfo.OrgLevel== (int)OrgLevel.Master)
            {
                sqlStr.Append($@" and Id not in (select id from organization where ParentId in (
                 select id from organization where ParentId={queryInfo.OrgId} and OrgLevel={(int)OrgLevel.Branch} and IsAllowContractSupplier = {(int)AllowContractSupplier.Yes}
                ))");
            }

            var query = _unitOfWork.QueryClient.SqlQueryable<OrganizationEntity>(sqlStr.ToString());
            //    .AddParameters(new SugarParameter[] {
            //        //new SugarParameter("SettlementOrgId",input.TasOrgId.Value)
            //}).Select((it) => new OrgSimpleDto
            //{
            //    Id = it.Id,
            //    Name = it.Name,
            //    ShortName = it.ShortName,
            //    OrgLevel = it.OrgLevel,
            //    LevelPath = it.LevelPath,
            //});

            //var searchPredict = _unitOfWork.QueryClient.Queryable<OrganizationEntity>()
            //    .Where(it=>it.LevelPath.StartsWith(query.LevelPath) && it.OrgLevel>(int)OrgLevel.Branch)
            //    .WhereIF(query.OrgLevel==(int)OrgLevel.Master,it=>SqlFunc.Subqueryable< OrganizationEntity>()
            //    .Where(o1=>SqlFunc.Subqueryable< OrganizationEntity>()
            //    .Where(o2=>)))
            //    .Select((it) => new OrgSimpleDto
            //    {
            //        Id = it.Id,
            //        Name = it.Name,
            //        ShortName = it.ShortName,
            //        OrgLevel = it.OrgLevel,
            //        LevelPath = it.LevelPath,
            //    });
            var totalCount = await query.CountAsync();
            var reslist = await query.ToPageListAsync(queryInfo.PageIndex, queryInfo.PageSize);
            var list = reslist.Select(it=> new OrgSimpleDto
            {
                Id = it.Id,
                Name = it.Name,
                ShortName = it.ShortName,
                OrgLevel = it.OrgLevel,
                LevelPath = it.LevelPath,
            }).ToList();
            var pageList = new PageDataList<OrgSimpleDto>(totalCount, list, queryInfo.PageIndex, queryInfo.PageSize);
            return pageList;
        }
    }
}
